from flask import Flask, request, jsonify, render_template
from flask_cors import CORS
import pymysql
from datetime import datetime, date, time
from decimal import Decimal
import os

app = Flask(__name__)
CORS(app)

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'mysql_learning'
}


def get_db_connection():
    """获取数据库连接 - 使用pymysql"""
    return pymysql.connect(
        host=DB_CONFIG['host'],
        user=DB_CONFIG['user'],
        password=DB_CONFIG['password'],
        database=DB_CONFIG['database'],
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )


from flask import Flask, request, jsonify, render_template
from flask_cors import CORS
import pymysql
from datetime import datetime, date, time
from decimal import Decimal
import os

app = Flask(__name__)
CORS(app)

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'mysql_learning'
}


def get_db_connection():
    """获取数据库连接 - 使用pymysql"""
    return pymysql.connect(
        host=DB_CONFIG['host'],
        user=DB_CONFIG['user'],
        password=DB_CONFIG['password'],
        database=DB_CONFIG['database'],
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )


def serialize_data(data):
    """
    序列化数据，处理MySQL返回的特殊类型
    """
    if isinstance(data, dict):
        return {key: serialize_data(value) for key, value in data.items()}
    elif isinstance(data, (list, tuple)):
        return [serialize_data(item) for item in data]
    elif isinstance(data, (bytes, bytearray)):
        try:
            return data.decode('utf-8')
        except:
            return str(data)
    elif isinstance(data, (datetime, date, time)):
        return data.isoformat()
    elif isinstance(data, Decimal):
        return float(data)
    elif data is None:
        return None
    else:
        return data


@app.route('/')
def index():
    return render_template('index.html')


@app.route('/api/sql/execute', methods=['POST'])
def execute_sql():
    data = request.json
    sql = data.get('sql', '').strip()

    if not sql:
        return jsonify({'success': False, 'error': 'SQL语句不能为空'})

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        cursor.execute(sql)

        sql_upper = sql.upper()
        if sql_upper.startswith('SELECT') or sql_upper.startswith('SHOW') or sql_upper.startswith('DESC'):
            result = cursor.fetchall()
            return jsonify({
                'success': True,
                'data': serialize_data(result),
                'type': 'query',
                'rowcount': len(result)
            })
        else:
            conn.commit()
            return jsonify({
                'success': True,
                'data': {'affected_rows': cursor.rowcount},
                'type': 'update',
                'rowcount': cursor.rowcount
            })

    except Exception as e:
        conn.rollback()
        return jsonify({'success': False, 'error': str(e)})
    finally:
        cursor.close()
        conn.close()


@app.route('/api/tables', methods=['GET'])
def get_tables():
    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        cursor.execute("SHOW TABLES")
        raw_tables = cursor.fetchall()

        tables = []
        total_rows = 0

        for table in raw_tables:
            table_name = list(table.values())[0] if table else None
            if not table_name:
                continue

            # 获取表结构
            cursor.execute(f"DESCRIBE `{table_name}`")
            structure = cursor.fetchall()

            # 获取数据量
            cursor.execute(f"SELECT COUNT(*) as count FROM `{table_name}`")
            count_result = cursor.fetchone()
            row_count = count_result['count'] if count_result else 0
            total_rows += row_count

            tables.append({
                'name': table_name,
                'structure': serialize_data(structure),
                'row_count': row_count
            })

        return jsonify({
            'success': True,
            'data': serialize_data(tables),
            'total_rows': total_rows
        })

    except Exception as e:
        print(f"Error: {str(e)}")
        return jsonify({'success': False, 'error': str(e)})
    finally:
        cursor.close()
        conn.close()


@app.route('/api/database/info', methods=['GET'])
def get_database_info():
    """获取数据库信息"""
    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        # 获取版本信息
        cursor.execute("SELECT VERSION() as version")
        version_result = cursor.fetchone()

        # 获取所有数据库
        cursor.execute("SHOW DATABASES")
        databases_result = cursor.fetchall()

        # 获取当前数据库的表数量和总行数
        cursor.execute("""
            SELECT 
                COUNT(*) as table_count,
                SUM(TABLE_ROWS) as total_rows
            FROM information_schema.TABLES 
            WHERE TABLE_SCHEMA = %s
        """, (DB_CONFIG['database'],))
        stats_result = cursor.fetchone()

        return jsonify({
            'success': True,
            'version': version_result['version'] if version_result else 'Unknown',
            'databases': [db['Database'] for db in databases_result],
            'current_database': DB_CONFIG['database'],
            'table_count': stats_result['table_count'] if stats_result else 0,
            'total_rows': stats_result['total_rows'] if stats_result and stats_result['total_rows'] else 0
        })

    except Exception as e:
        return jsonify({'success': False, 'error': str(e)})
    finally:
        cursor.close()
        conn.close()


@app.route('/api/table/<table_name>/structure', methods=['GET'])
def get_table_structure(table_name):
    """获取表结构详情"""
    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        # 获取表结构
        cursor.execute(f"DESCRIBE `{table_name}`")
        structure = cursor.fetchall()

        return jsonify({
            'success': True,
            'structure': serialize_data(structure)
        })

    except Exception as e:
        return jsonify({'success': False, 'error': str(e)})
    finally:
        cursor.close()
        conn.close()


@app.route('/api/table/<table_name>', methods=['GET'])
def get_table_data(table_name):
    """获取表数据"""
    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        # 获取数据
        cursor.execute(f"SELECT * FROM `{table_name}` LIMIT 50")
        data = cursor.fetchall()

        # 获取表结构
        cursor.execute(f"DESCRIBE `{table_name}`")
        structure = cursor.fetchall()

        return jsonify({
            'success': True,
            'data': serialize_data(data),
            'structure': serialize_data(structure)
        })

    except Exception as e:
        return jsonify({'success': False, 'error': str(e)})
    finally:
        cursor.close()
        conn.close()


@app.route('/api/database/stats', methods=['GET'])
def get_database_stats():
    """获取数据库详细统计信息"""
    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        # 获取表统计信息
        cursor.execute("""
            SELECT 
                TABLE_NAME,
                TABLE_ROWS,
                DATA_LENGTH,
                INDEX_LENGTH,
                CREATE_TIME
            FROM information_schema.TABLES 
            WHERE TABLE_SCHEMA = %s
            ORDER BY TABLE_ROWS DESC
        """, (DB_CONFIG['database'],))
        table_stats = cursor.fetchall()

        total_stats = {
            'table_count': len(table_stats),
            'total_rows': sum(table['TABLE_ROWS'] or 0 for table in table_stats),
            'total_size': sum((table['DATA_LENGTH'] or 0) + (table['INDEX_LENGTH'] or 0) for table in table_stats)
        }

        return jsonify({
            'success': True,
            'table_stats': serialize_data(table_stats),
            'total_stats': total_stats
        })

    except Exception as e:
        return jsonify({'success': False, 'error': str(e)})
    finally:
        cursor.close()
        conn.close()






if __name__ == '__main__':
    app.run(debug=True, port=5000)